﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MIMDesignShaft
{
    class SqlHelper
    {
        //定义一个连接字符串，读取配置文件中名为MIMDesignShaftConnectionString的connectionString
        private static string constr = ConfigurationManager.ConnectionStrings["MIMDesignShaftConnectionString"].ConnectionString;

        //1.执行增（insert）删（delete）改（update）的方法
        //ExecuteNonQuery();
        /// <summary>
        /// 执行增（insert）删（delete）改（update）的方法
        /// </summary>
        /// <param name="sql"> sql语句</param>
        /// <param name="pms">传入的参数</param>
        /// <returns> 返回影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    if (pms != null)
                    {
                        com.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return com.ExecuteNonQuery();
                }
            }
        }

        //2.执行查询，返回单个值
        //ExecuteScalar()
        /// <summary>
        /// 执行查询，返回单个值
        /// </summary>
        /// <param name="sql"> 传入的sql语句字符串</param>
        /// <param name="pms">sql语句中的参数</param>
        /// <returns>返回一个Object</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    if (pms != null)
                    {
                        com.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return com.ExecuteScalar();

                }
            }
        }
        //3.执行查询，返回多个值
        //ExecuteReader();
        /// <summary>
        /// 执行查询，返回多个值
        /// </summary>
        /// <param name="sql"> 传入的sql语句字符串</param>
        /// <param name="pms">sql语句中的参数</param>
        /// <returns>返回一个Reader,用于获取返回的每个结果</returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(constr);
            Console.WriteLine("连接成功");
            using (SqlCommand com = new SqlCommand(sql, con))
            {
                //如果参数不为空，
                if (pms != null)
                {
                    com.Parameters.AddRange(pms);
                }
                try
                {
                    //System.Data.CommandBehavior.CloseConnection这个枚举参数 ，表示将来使用完毕sqlDataReader后，在关闭reader的同时，在sqlDataReader内部会将关联的connection对象也关闭
                    con.Open();
                    return com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch
                {
                    con.Close();
                    con.Dispose();
                    throw;
                }

            }
        }
        /// <summary>
        /// 执行查询,返回datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable SqlDataAdapter(string sql)
        {
            SqlConnection con = new SqlConnection(constr);
            try
            {
                con.Open();
                //创建SqlDataAdapter对象
                SqlDataAdapter s = new SqlDataAdapter(sql, con);
                //创建DataTable对象
                DataTable dt = new DataTable();
                //使用fill方法填充DataTable
                s.Fill(dt);
                return dt;
            }
            catch
            {
                con.Close();
                con.Dispose();
                throw;
            }
        }
        /// <summary>
        /// 执行查询，返回dataset
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public static DataSet Query(string SQLString,DataSet ds,string tableName)
        {
            using (SqlConnection connection = new SqlConnection(constr))
            {
                //DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, tableName);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        /// <summary>
        /// 通过事务批量处理sql语句
        /// </summary>
        /// <param name="keyValues">key：sql语句 values：参数</param>
        /// <returns>执行结果</returns>
        public static bool sqlTransaction(Dictionary<string,SqlParameter[]> keyValues)
        {
            //打开数据库连接
            SqlConnection con = new SqlConnection(constr);
            con.Open();
            //声明sql事务
            SqlTransaction myTrans;
            //sql命令对象
            SqlCommand mycmd = new SqlCommand();
            //声明命令对象的连接数据库
            mycmd.Connection = con;
            //开启事务
            myTrans = con.BeginTransaction();
            //设置将在命令对象中执行的事务
            mycmd.Transaction = myTrans;
            try
            {
                //遍历字符串数组中的sql语句
                foreach (KeyValuePair<string, SqlParameter[]> item in keyValues)
                {
                    mycmd.CommandText = item.Key;
                    if (item.Value != null)
                    {
                        //清楚上一次传递的参数
                        mycmd.Parameters.Clear();
                        mycmd.Parameters.AddRange(item.Value);
                    }
                    mycmd.ExecuteNonQuery();
                }
                //事务提交
                myTrans.Commit();
                return true;
            }
            catch (SqlException ex)
            {
                //执行错误，事务回滚
                if (myTrans != null)
                    myTrans.Rollback();
                return false;
            }
            finally
            {
                //关闭连接                
                if (con != null)
                    con.Close();
            }

        }
    }
}
